package com.zglc.ms.shop.dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.zglc.ms.shop.model.ShopHomeTemplate;
import com.zglc.ms.shop.model.ShopHomeTemplateVo;
import com.zglc.ms.shop.model.ShopProduct;

@Repository
public class HomeTemplateDao {

	private Logger logger = LoggerFactory.getLogger(getClass());

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	public int insertShopHomeTemplate(ShopHomeTemplate shopHomeTemplate) {
		final String sql = "INSERT INTO shop_home_template( area_name, summary, "
				+ " weight, status, created_at, updated_at)"
				+ " VALUES ( ?, ?,   ?, ?, NOW(), NOW())";
		logger.debug("插入Templatesql： " + sql);
		logger.debug("插入参数： " + shopHomeTemplate.toString());
		KeyHolder keyHolder = new GeneratedKeyHolder();
		try{	
			jdbcTemplate.update(new PreparedStatementCreator() {
	
				@Override
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
					PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
					ps.setString(1, shopHomeTemplate.getAreaName());
					ps.setString(2, shopHomeTemplate.getSummary());
					
					ps.setInt(3, shopHomeTemplate.getWeight());
					ps.setBoolean(4, shopHomeTemplate.isStatus());
					return ps;
				}
			}, keyHolder);
		}catch(Exception e){
			e.printStackTrace();
			logger.debug(e.toString());
			return -1;
		}
		return keyHolder.getKey().intValue();
	}
	
	public int UpdateShopHomeTemplate(ShopHomeTemplate shopHomeTemplate) {
		final String sql = "REPLACE INTO shop_product(  area_name, summary, "
				+ " weight, status, created_at, updated_at, id) VALUES ( ?, ?,   ?, ?, NOW(), NOW(), ?)";
		logger.debug("更新Template  sql： " + sql);
		logger.debug("更新参数： " + shopHomeTemplate.toString());
		KeyHolder keyHolder = new GeneratedKeyHolder();
		try{	
			jdbcTemplate.update(new PreparedStatementCreator() {
	
				@Override
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
					PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
					ps.setString(1, shopHomeTemplate.getAreaName());
					ps.setString(2, shopHomeTemplate.getSummary());
					
					ps.setInt(3, shopHomeTemplate.getWeight());
					ps.setBoolean(4, shopHomeTemplate.isStatus());
					ps.setInt(5, shopHomeTemplate.getId());
					return ps;
				}
			}, keyHolder);
		}catch(Exception e){
			e.printStackTrace();
			logger.debug(e.toString());
			return -1;
		}
		return keyHolder.getKey().intValue();
	}
	
	
	public ShopHomeTemplate getShopHomeTemplateByCode( int templateId) {
		final String sql = "SELECT * FROM shop_home_template  WHERE id = ? ";
		
		try {
			ShopHomeTemplate info = jdbcTemplate.queryForObject(sql, new Object[]{templateId}, new RowMapper<ShopHomeTemplate>() {

				@Override
				public ShopHomeTemplate mapRow(ResultSet rs, int rowNum) throws SQLException {
					ShopHomeTemplate info = setShopHomeTemplate(rs);
					return info;
				}
			});
			
			return info;
		} catch (DataAccessException e) {
			logger.info("getShopProductByCode e = " + e.getLocalizedMessage());
		}
		
		return null;
	}
	
	
	private ShopHomeTemplate setShopHomeTemplate(ResultSet rs) throws SQLException{
		ShopHomeTemplate info = new ShopHomeTemplate();
		info.setId(rs.getInt("id"));
		info.setAreaName(rs.getString("area_name"));
		info.setSummary(rs.getString("summary"));
		info.setWeight(rs.getInt("weight"));
		info.setStatus(rs.getBoolean("status"));
		info.setCreatedAt(rs.getDate("created_at"));
		info.setUpdatedAt(rs.getDate("updated_at"));
		return info;
	}
	
	private ShopHomeTemplateVo setShopHomeTemplateVo(ResultSet rs) throws SQLException{
		ShopHomeTemplateVo info = new ShopHomeTemplateVo();
		info.setId(rs.getInt("id"));
		info.setAreaName(rs.getString("area_name"));
		info.setSummary(rs.getString("summary"));
		return info;
	}
	
	public List<ShopHomeTemplate> getShopHomeTemplateByAll() {
		
		final String sql = "SELECT  * FROM shop_home_template WHERE status = true ORDER BY weight desc, updated_at desc";
		try {
			List<ShopHomeTemplate> users = jdbcTemplate.query(sql, new Object[] {},
					new ResultSetExtractor<List<ShopHomeTemplate>>() {
						public List<ShopHomeTemplate> extractData(ResultSet rs)
								throws SQLException, DataAccessException {
							List<ShopHomeTemplate> list = new ArrayList<ShopHomeTemplate>();
							while (rs.next()) {
								ShopHomeTemplate info = setShopHomeTemplate(rs);
							list.add(info);
						}
						return list;
				}
			});
			return users;
		} catch (DataAccessException e) {
			logger.error("not exist getShopHomeTemplateByAll by all = "  + "|" + e.getLocalizedMessage());
			return null;
		}
	}
	
	public List<ShopHomeTemplateVo> getShopHomeTemplateVoByAll() {
		
		final String sql = "SELECT  * FROM shop_home_template WHERE status = true ORDER BY weight desc, updated_at desc";
		try {
			List<ShopHomeTemplateVo> users = jdbcTemplate.query(sql, new Object[] {},
					new ResultSetExtractor<List<ShopHomeTemplateVo>>() {
						public List<ShopHomeTemplateVo> extractData(ResultSet rs)
								throws SQLException, DataAccessException {
							List<ShopHomeTemplateVo> list = new ArrayList<ShopHomeTemplateVo>();
							while (rs.next()) {
								ShopHomeTemplateVo info = setShopHomeTemplateVo(rs);
							list.add(info);
						}
						return list;
				}
			});
			return users;
		} catch (DataAccessException e) {
			logger.error("not exist getShopHomeTemplateVoByAll by all = "  + "|" + e.getLocalizedMessage());
			return null;
		}
	}
	
	
}